package procedure;



/**
 * Collection of SQL strings for operations related to ScenarioCollections.
 * 
 * @author Daniel Funk
 * @author Yudi Xue
 */
public interface SQLScenarioCollectionStringsI {

	public static final String SCOLLECTION_INSERT = "INSERT INTO ScenarioCollection (sc_identity, sc_version, sc_update_time, sc_name, sc_creator, sc_creationDate, sc_description, sc_mv_identity) "
			+ "VALUES ('%s', '%s', CURRENT_TIMESTAMP, '%s', '%s', CURRENT_TIMESTAMP, '%s', '%s')";

	public static final String SCOLLECTION_SELECT = "SELECT sc_identity, sc_version, sc_update_time, sc_name, sc_creator, sc_creationDate, sc_description, sc_mv_identity "
			+ "FROM ScenarioCollection WHERE sc_identity = '%s'";

	public static final String SCOLLECTION_SELECT_NAME = "SELECT sc_name FROM ScenarioCollection WHERE sc_identity = '%s'";

	public static final String SCOLLECTION_SELECT_IDENTITY = "SELECT sc_identity FROM ScenarioCollection WHERE sc_identity = '%s'";
	public static final String SCOLLECTION_SELECT_VERSION = "SELECT sc_version FROM ScenarioCollection WHERE sc_identity = '%s'";
	public static final String SCOLLECTION_SELECT_LAST_UPDATE_TIME = "SELECT sc_update_time FROM ScenarioCollection WHERE sc_identity = '%s'";

	public static final String SCOLLECTION_SELECT_CREATOR = "SELECT sc_creator FROM ScenarioCollection WHERE sc_identity = '%s'";
	public static final String SCOLLECTION_SELECT_CREATIONDATE = "SELECT sc_creationDate FROM ScenarioCollection WHERE sc_identity = '%s'";
	public static final String SCOLLECTION_SELECT_DESCRIPTION = "SELECT sc_description FROM ScenarioCollection WHERE sc_identity = '%s'";
	public static final String SCOLLECTION_SELECT_MVERSIONID = "SELECT sc_mv_identity FROM ScenarioCollection WHERE sc_identity = '%s'";

	public static final String SCOLLECTION_SELECT_SCENARIOIDS = "SELECT s_identity FROM Scenario WHERE s_sc_identity = '%s'";
	public static final String SCOLLECTION_SELECT_SCENARIOIDSANDNAMES = "SELECT s_identity, s_name FROM Scenario WHERE s_sc_identity = '%s'";

	public static final String SCOLLECTION_SELECT_MOSTRECENTSCENARIOID = "SELECT s_identity FROM Scenario WHERE s_sc_identity = '%s' "
			+ "ORDER BY s_creationDate DESC LIMIT 1";

	public static final String SCOLLECTION_SELECT_ASSOCIATEDFILEIDS = "SELECT sc2f_f_identity FROM SC2F WHERE sc2f_sc_identity = '%s'";
	public static final String SCOLLECTION_SELECT_ASSOCIATEDFILE_NAMES = "SELECT File.f_name FROM File WHERE File.f_identity IN (SELECT sc2f_f_identity FROM SC2F WHERE sc2f_sc_identity = '%s')";

	public static final String SCOLLECTION_DELETE_ASSOCIATEDFILEIDS = "DELETE FROM File WHERE File.f_identity IN (SELECT sc2f_f_identity FROM SC2F WHERE sc2f_sc_identity = '%s')";

	public static final String SCOLLECTION_SET_IDENTITY = "UPDATE scenarioCollection SET sc_identity = '%s', sc_update_time = CURRENT_TIMESTAMP WHERE sc_identity = '%s'";
	public static final String SCOLLECTION_SET_VERSION = "UPDATE scenarioCollection SET sc_version = '%s' , sc_update_time = CURRENT_TIMESTAMP WHERE sc_identity = '%s'";

	public static final String SCOLLECTION_SET_NAME = "UPDATE ScenarioCollection SET sc_name = '%s' , sc_update_time = CURRENT_TIMESTAMP WHERE sc_identity = '%s'";
	public static final String SCOLLECTION_SET_CREATOR = "UPDATE scenarioCollection SET sc_creator = '%s' , sc_update_time = CURRENT_TIMESTAMP WHERE sc_identity = '%s'";

	public static final String SCOLLECTION_SET_DESCRIPTION = "UPDATE ScenarioCollection SET sc_description = '%s' , sc_update_time = CURRENT_TIMESTAMP WHERE sc_identity = '%s'";

	public static final String SCOLLECTION_DELETE = "DELETE FROM ScenarioCollection WHERE sc_identity = '%s'";

	// new id, new version, old id, old id, old id, mv id
	public static final String SCOLLECTION_COPY = "INSERT INTO ScenarioCollection (sc_identity, sc_version, sc_update_time, sc_name, sc_creator, sc_creationDate, sc_description, sc_mv_identity) "
			+ "VALUES ('%s', '%s', "
			+ "CURRENT_TIMESTAMP,"
			+ "(SELECT sc_name FROM ScenarioCollection WHERE sc_identity = '%s'), "
			+ "(SELECT sc_creator FROM ScenarioCollection WHERE sc_identity = '%s'), "
			+ "CURRENT_TIMESTAMP, "
			+ "(SELECT sc_description FROM ScenarioCollection WHERE sc_identity = '%s'), "
			+ "'%s')";

}
